/**
 * File Name	: SqlMapGenerator.java
 * Author		: Administrator:Adelwin
 * Create Date	: Mar 31, 2006:1:23:11 AM
 *
 * Copyright (c) 2006 Adelwin. All Rights Reserved. <BR>
 * <BR>
 * This software contains confidential and proprietary information of
 * Adelwin. ("Confidential Information").<BR>
 * <BR>
 * Such Confidential Information shall not be disclosed and it shall
 * only be used in accordance with the terms of the license agreement
 * entered into with Firium; other than in accordance with the written
 * permission of Firium. <BR>
 *
 *
 */

package com.uobgroup.cmt.conversion.tool;

import java.awt.Container;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Vector;

import javax.swing.JButton;
import javax.swing.JCheckBox;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JPasswordField;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.border.BevelBorder;
import javax.swing.border.EtchedBorder;

import com.uobgroup.cmt.base.util.StringUtil;

public class SqlMapGenerator extends JFrame implements ActionListener {

	private static final long serialVersionUID = 4460197799060671472L;

	/*********************************************************************************************/
	// main variables
	private String sqlMap;

	private String[] dbDrivers = { "oracle.jdbc.driver.OracleDriver", "com.mysql.jdbc.Driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver" };

	private Vector<String> tableNames = new Vector<String>();

	private Vector<String> catalogs = new Vector<String>();

	private String packageName;

	private Connection connection;

	private Statement statement;

	private ResultSet resultSet;

	private DatabaseMetaData dbMetaData;

	private ResultSet rsMetaData;

	private String dbUrl;

	private String dbDriver;

	private String dbUserName;

	private String dbPassword;

	private String tableName;

	private String catalog;

	private boolean camelCase = false;

	private boolean pluralTableNames = false;

	// helper vars
	private String LITERAL_TAB = "\t";

	private String LITERAL_SPACE = " ";

	private String LITERAL_CRLF = "\n";

	private String LITERAL_SQLMAP_HEADER = "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>" + LITERAL_CRLF + "<!--<!DOCTYPE sqlMap SYSTEM \"../dtd/sql-map-2.dtd\">-->" + LITERAL_CRLF
			+ "<!--<!DOCTYPE sqlMap PUBLIC \"-//iBATIS.com//DTD SQL Map 2.0//EN\" \"http://www.ibatis.com/dtd/sql-map-2.dtd\">-->" + LITERAL_CRLF
			+ "<!DOCTYPE sqlMap PUBLIC \"-//iBATIS.com//DTD SQL Map 2.0//EN\" \"G:/Workspace/SOL/DiamondWeb/config/dtd/sql-map-2.dtd\">";

	// gui variables
	private JLabel lblTableName = new JLabel("Table Name");

	private JLabel lblDbUrl = new JLabel("DB URL");

	private JLabel lblDbUserName = new JLabel("User Name");

	private JLabel lblDbPassword = new JLabel("Password");

	private JLabel lblDbDriver = new JLabel("DB Driver");

	private JLabel lblCatalog = new JLabel("Catalog");

	private JComboBox cbTableName = new JComboBox(tableNames);

	private JTextField txtDbUrl = new JTextField("jdbc:mysql://127.0.0.1:3306/diamond");

	// private JTextField txtDbUrl = new
	// JTextField("jdbc:oracle:thin:@127.0.0.1:1521:ORCL");
	private JTextField txtDbUserName = new JTextField("DIAMOND");

	private JPasswordField txtDbPassword = new JPasswordField("password");

	private JComboBox cbDbDriver = new JComboBox(dbDrivers);

	private JComboBox cbDbCatalog = new JComboBox(catalogs);

	private JCheckBox chkCamelCase = new JCheckBox("CamelCase");

	private JCheckBox chkPluralTableNames = new JCheckBox("PluralTableNames");

	private JTextArea txtSqlMap = new JTextArea();

	private JScrollPane scrolledTxtSqlMap = new JScrollPane(txtSqlMap);

	private JTextArea txtClassDeclaration = new JTextArea();

	private JScrollPane scrolledTxtClassDeclaration = new JScrollPane(txtClassDeclaration);

	private JButton btnGo = new JButton("Go!");

	private JButton btnExtractTableNames = new JButton("Extract Table!");

	private JButton btnConnect = new JButton("Connect!");

	private Container container = this.getContentPane();

	private JPanel panelInput = new JPanel();

	private JPanel panelSqlMap = new JPanel();

	private JPanel panelClassDeclaration = new JPanel();

	/*********************************************************************************************/
	public void createMenu() {
		JMenuBar menuBar = new JMenuBar();
		setJMenuBar(menuBar);
		JMenu mnuFile = new JMenu("File");
		menuBar.add(mnuFile);
		JMenuItem mnuFileReset = new JMenuItem("Reset");
		mnuFileReset.setActionCommand("Reset");
		mnuFileReset.addActionListener(this);
		mnuFile.add(mnuFileReset);
		JMenuItem mnuFileExit = new JMenuItem("Exit");
		mnuFileExit.setActionCommand("Exit");
		mnuFileExit.addActionListener(this);
		mnuFile.add(mnuFileExit);
		JMenu mnuHelp = new JMenu("Help");
		menuBar.add(mnuHelp);
		JMenuItem mnuHelpAbout = new JMenuItem("About");
		mnuHelpAbout.setActionCommand("About");
		mnuHelpAbout.addActionListener(this);
		mnuHelp.add(mnuHelpAbout);
	}

	/*********************************************************************************************/
	public void createPanelInput() {
		// init the panel
		panelInput.setBorder(new EtchedBorder(BevelBorder.LOWERED, null, null));
		panelInput.setBounds(100, 10, 600, 155);
		panelInput.setLayout(null);
		// add controls to panel
		// add db driver label
		lblDbDriver.setBounds(10, 5, 70, 20);
		panelInput.add(lblDbDriver);
		// add db driver list
		cbDbDriver.setBounds(85, 5, 500, 20);
		cbDbDriver.setEditable(false);
		panelInput.add(cbDbDriver);
		// add db user name label
		lblDbUserName.setBounds(10, 30, 70, 20);
		panelInput.add(lblDbUserName);
		// add db user name text
		txtDbUserName.setBounds(85, 30, 200, 20);
		txtDbUserName.setEditable(true);
		panelInput.add(txtDbUserName);
		// add db password label
		lblDbPassword.setBounds(10, 55, 70, 20);
		panelInput.add(lblDbPassword);
		// add db password text
		txtDbPassword.setBounds(85, 55, 200, 20);
		txtDbPassword.setEditable(true);
		panelInput.add(txtDbPassword);
		// add db url label
		lblDbUrl.setBounds(10, 80, 70, 20);
		panelInput.add(lblDbUrl);
		// add db url text
		txtDbUrl.setBounds(85, 80, 200, 20);
		txtDbUrl.setEditable(true);
		panelInput.add(txtDbUrl);
		// add button connect
		btnConnect.setBounds(300, 80, 90, 20);
		btnConnect.setActionCommand("Connect");
		btnConnect.addActionListener(this);
		panelInput.add(btnConnect);
		// add catalog name
		lblCatalog.setBounds(10, 105, 70, 20);
		panelInput.add(lblCatalog);
		// add catalog combo
		cbDbCatalog.setBounds(85, 105, 200, 20);
		cbDbCatalog.setEditable(false);
		panelInput.add(cbDbCatalog);
		// add go button
		btnExtractTableNames.setBounds(300, 105, 150, 20);
		btnExtractTableNames.setActionCommand("Extract");
		btnExtractTableNames.addActionListener(this);
		panelInput.add(btnExtractTableNames);
		// add table name
		lblTableName.setBounds(10, 130, 70, 20);
		panelInput.add(lblTableName);
		// add table name combo
		cbTableName.setBounds(85, 130, 200, 20);
		cbTableName.setEditable(false);
		panelInput.add(cbTableName);
		// add go button
		btnGo.setBounds(300, 130, 90, 20);
		btnGo.setActionCommand("Go");
		btnGo.addActionListener(this);
		panelInput.add(btnGo);
		// add camel case check box
		chkCamelCase.setBounds(400, 130, 90, 20);
		chkCamelCase.setActionCommand("CamelCase");
		chkCamelCase.addActionListener(this);
		panelInput.add(chkCamelCase);
		// add plural table name check box
		chkPluralTableNames.setBounds(500, 130, 90, 20);
		chkPluralTableNames.setActionCommand("PluralTableNames");
		chkPluralTableNames.addActionListener(this);
		panelInput.add(chkPluralTableNames);
		// add to container
		container.add(panelInput);
	}

	/*********************************************************************************************/
	public void createPanelSqlMap() {
		// init the panel
		panelSqlMap.setBorder(new EtchedBorder(BevelBorder.LOWERED, null, null));
		panelSqlMap.setBounds(10, 170, 385, 370);
		panelSqlMap.setLayout(null);
		// add control to panel
		scrolledTxtSqlMap.setBounds(5, 5, 375, 360);
		txtSqlMap.setTabSize(4);
		txtSqlMap.setFont(new Font("courier new", 0, 10));
		panelSqlMap.add(scrolledTxtSqlMap);
		// add to container
		container.add(panelSqlMap);
	}

	/*********************************************************************************************/
	public void createPanelClassDeclaration() {
		// init the panel
		panelClassDeclaration.setBorder(new EtchedBorder(BevelBorder.LOWERED, null, null));
		panelClassDeclaration.setBounds(395, 170, 385, 370);
		panelClassDeclaration.setLayout(null);
		// add control to panel
		scrolledTxtClassDeclaration.setBounds(5, 5, 375, 360);
		txtClassDeclaration.setTabSize(4);
		txtClassDeclaration.setFont(new Font("courier new", 0, 10));
		panelClassDeclaration.add(scrolledTxtClassDeclaration);
		// add to container
		container.add(panelClassDeclaration);
	}

	/*********************************************************************************************/
	public void initGUI() {
		// absulut layout
		container.setLayout(null);

		createMenu();
		createPanelInput();
		createPanelSqlMap();
		createPanelClassDeclaration();
	}

	/*********************************************************************************************/
	// Main Constructor
	/*********************************************************************************************/
	public SqlMapGenerator() {
		super("Sql Map Generator");
		// register window listener
		this.addWindowListener(new WindowAdapter() {

			public void windowClosing(WindowEvent e) {
				try {
					resultSet.close();
					statement.close();
					connection.close();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} finally {
					System.exit(0);
				}
			}
		});
		initGUI();
		setResizable(false);
		setSize(800, 600);
		setVisible(true);
	}

	/*********************************************************************************************/
	// main method
	/*********************************************************************************************/
	public static void main(String[] args) {
		@SuppressWarnings("unused")
		SqlMapGenerator app = new SqlMapGenerator();
	}

	/*********************************************************************************************/
	// Listeners Methods
	/*********************************************************************************************/
	public void actionPerformed(ActionEvent e) {
		String actionCommand = e.getActionCommand();
		if (actionCommand.equals("Exit"))
			System.exit(0);
		if (actionCommand.equals("About"))
			JOptionPane.showMessageDialog(this, "SqlMapGenerator");
		if (actionCommand.equals("Connect"))
			connectDb();
		if (actionCommand.equals("Extract"))
			extractTables();
		if (actionCommand.equals("CamelCase"))
			this.camelCase = !this.camelCase;
		if (actionCommand.equals("PluralTableNames"))
			this.pluralTableNames = !this.pluralTableNames;
		if (actionCommand.equals("Go")) {
			packageName = JOptionPane.showInputDialog(this, "enter package name", "com.si.diamond.appl.model");
			if (packageName != null) {
				generateSqlMap();
				generateClassDeffinition();
			}
		}
	}

	/*********************************************************************************************/
	// Custom Database Meta Data Methods
	/*********************************************************************************************/

	public ResultSet getCatalogs() throws SQLException {
		ResultSet rs;
		if (this.dbDriver.indexOf("Oracle") >= 0) {
			rs = connection.createStatement().executeQuery("select distinct owner from all_tables");
		} else {
			dbMetaData = (DatabaseMetaData) connection.getMetaData();
			rs = dbMetaData.getCatalogs();
		}
		return rs;
	}

	public ResultSet getTables(String catalog) throws SQLException {
		ResultSet rs;
		if (this.dbDriver.indexOf("Oracle") >= 0) {
			rs = connection.createStatement().executeQuery("select distinct 1 as col1, 2 as col2, table_name from all_tables where owner = '" + catalog + "'");
		} else {
			dbMetaData = (DatabaseMetaData) connection.getMetaData();
			rs = dbMetaData.getTables(catalog, null, null, null);
		}
		return rs;
	}

	public ResultSet getColumns(String catalog, String tableName) throws SQLException {
		ResultSet rs;
		if (this.dbDriver.indexOf("Oracle") >= 0) {
			rs = connection.createStatement().executeQuery("select 1 as col1, 2 as col2, 3 as col3, column_name from all_tab_columns where table_name = '" + tableName + "' and owner = '" + catalog + "'order by column_id");
		} else {
			dbMetaData = (DatabaseMetaData) connection.getMetaData();
			rs = dbMetaData.getColumns(catalog, null, tableName, null);
		}
		return rs;
	}

	public ResultSet getColumnTypes(String catalog, String tableName) throws SQLException {
		ResultSet rs;
		if (this.dbDriver.indexOf("Oracle") >= 0) {
			rs = connection.createStatement().executeQuery("select 1 as col1, 2 as col2, 3 as col3, 4 as col4, 5 as col5, data_type from all_tab_columns where table_name = '" + tableName + "' order by column_id");
		} else {
			dbMetaData = (DatabaseMetaData) connection.getMetaData();
			rs = dbMetaData.getColumns(catalog, null, tableName, null);
		}
		return rs;
	}

	/*********************************************************************************************/
	// Helper Methods
	/*********************************************************************************************/

	public List<String> createListFromMetaData(ResultSet rsMetaData, int metaDataColumn, boolean camelCase) {
		try {
			List<String> retVal = new ArrayList<String>();
			while (rsMetaData.next()) {
				if (camelCase) {
					retVal.add(StringUtil.createCamelCase(rsMetaData.getString(metaDataColumn), "_"));
				} else {
					String columnName = rsMetaData.getString(metaDataColumn);
					char[] columnNameChar = columnName.toCharArray();
					columnNameChar[0] = Character.toUpperCase(columnNameChar[0]);
					retVal.add(new String(columnNameChar));
				}
			}
			return retVal;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public List<String> createListFromMetaData(ResultSet rsMetaData, int metaDataColumn) {
		try {
			List<String> retVal = new ArrayList<String>();
			while (rsMetaData.next()) {
				retVal.add(rsMetaData.getString(metaDataColumn));
			}
			return retVal;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/*********************************************************************************************/
	// Custom Methods
	/*********************************************************************************************/

	public void connectDb() {
		try {
			this.dbDriver = (String) cbDbDriver.getSelectedItem();
			this.dbUserName = txtDbUserName.getText();
			this.dbPassword = new String(txtDbPassword.getPassword());
			this.dbUrl = txtDbUrl.getText();
			DriverManager.registerDriver((Driver) Class.forName(this.dbDriver).newInstance());
			connection = DriverManager.getConnection(this.dbUrl, this.dbUserName, this.dbPassword);
			ResultSet rsCatalogs = getCatalogs();
			// rsMetaData.beforeFirst();
			while (rsCatalogs.next()) {
				catalogs.add(rsCatalogs.getString(1));
			}
			cbDbCatalog.repaint();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(this, "Unable to Connect");
		} catch (InstantiationException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(this, "Unable to Connect");
		} catch (IllegalAccessException e) {
			e.printStackTrace();
			JOptionPane.showMessageDialog(this, "Unable to Connect");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void extractTables() {
		try {
			this.catalog = (String) cbDbCatalog.getSelectedItem();
			ResultSet rsTables = getTables(this.catalog);
			// rsMetaData.beforeFirst();
			while (rsTables.next()) {
				tableNames.add(rsTables.getString(3));
			}
			cbTableName.repaint();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void generateSqlMap() {
		this.tableName = (String) this.cbTableName.getSelectedItem();
		List<String> columnName = new ArrayList<String>();
		List<String> nativeColumnName = new ArrayList<String>();
		List<String> columnType = new ArrayList<String>();
		List<String> nativeColumnType = new ArrayList<String>();

		try {
			ResultSet rsColumnNames = getColumns(this.catalog, this.tableName);
			columnName = createListFromMetaData(rsColumnNames, 4, this.camelCase);
			ResultSet rsColumnTypes = getColumnTypes(this.catalog, this.tableName);
			columnType = createListFromMetaData(rsColumnTypes, 6, false);

			rsColumnNames = getColumns(this.catalog, this.tableName);
			nativeColumnName = createListFromMetaData(rsColumnNames, 4);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String tableModelName = "";
		tableModelName = this.tableName.toLowerCase().substring(0, tableName.length() - (this.pluralTableNames ? 1 : 0));
		tableModelName = this.camelCase ? StringUtil.createCamelCase(tableModelName, "_") : tableModelName;

		sqlMap = new String();
		sqlMap += (LITERAL_SQLMAP_HEADER + LITERAL_CRLF);
		sqlMap += LITERAL_CRLF;
		sqlMap += ("<sqlMap>" + LITERAL_CRLF);
		sqlMap += createParam(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createResult(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createGetById(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createGetByExample(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createInsert(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createUpdate(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createDelete(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += createAudit(tableModelName, columnName, columnType, nativeColumnName, nativeColumnType);
		sqlMap += ("</sqlMap>" + LITERAL_CRLF);

		txtSqlMap.setText(sqlMap);
	}

	private String createParam(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<parameterMap id=\"" + tableName + ".param\" class=\"" + packageName + "." + tableName + "Model\">" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column;
			String field;
			column = (String) columnName.get(i);
			char[] columnC = column.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + "<parameter " + StringUtil.padString("property=\"" + field + "\"", 35, " ", StringUtil.PAD_ATREAR)
					+ StringUtil.padString("jdbcType=\"" + getJdbcType((String) columnType.get(i)) + "\"", 35, " ", StringUtil.PAD_ATREAR)
					+ StringUtil.padString("javaType=\"" + getJavaType((String) columnType.get(i)) + "\"", 35, " ", StringUtil.PAD_ATREAR) + LITERAL_SPACE + "/>" + LITERAL_SPACE + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + "</parameterMap>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);

		return sqlMap;
	}

	private String createResult(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<resultMap id=\"" + tableName + ".result\" class=\"" + packageName + "." + tableName + "Model\">" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column;
			String field;
			column = (String) columnName.get(i);
			char[] columnC = column.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + "<result " + StringUtil.padString("property=\"" + field + "\"", 35, " ", StringUtil.PAD_ATREAR)
					+ StringUtil.padString("jdbcType=\"" + getJdbcType((String) columnType.get(i)) + "\"", 35, " ", StringUtil.PAD_ATREAR)
					+ StringUtil.padString("javaType=\"" + getJavaType((String) columnType.get(i)) + "\"", 35, " ", StringUtil.PAD_ATREAR) + "column=\"" + nativeColumnName.get(i) + "\"" + LITERAL_SPACE + "/>" + LITERAL_SPACE + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + "</resultMap>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createGetById(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		String column = (String) nativeColumnName.get(0);
		String field = (String) columnName.get(0);
		// column = (String) columnName.get(0);
		// char[] columnC = column.toCharArray();
		// columnC[0] = Character.toLowerCase(columnC[0]);
		// field = new String(columnC);
		sqlMap += (LITERAL_TAB + "<select id=\"" + tableName + ".getById\"" + " parameterClass=\"java.lang.String\" resultMap=\"" + tableName + ".result\"" + ">" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "SELECT * FROM " + this.tableName.toUpperCase() + " WHERE " + column + "=#" + field + "#" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</select>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createGetByExample(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<select id=\"" + tableName + ".getByExample\" parameterMap=\"" + tableName + ".param\" resultMap=\"" + tableName + ".result\">" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "SELECT *" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "FROM " + this.tableName.toUpperCase() + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "<dynamic prepend=\"WHERE\">" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column = (String) nativeColumnName.get(i);
			String field = (String) columnName.get(i);
			char[] columnC = field.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			String fieldLowerCase = new String(columnC);
			// column = (String) columnName.get(i);
			// char[] columnC = column.toCharArray();
			// columnC[0] = Character.toLowerCase(columnC[0]);
			// field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "<isNotNull property=\"" + fieldLowerCase + "\" prepend=\"AND\">" + LITERAL_CRLF);
			// if (columnType.get(i).equals("datetime")) {
			// <![CDATA[CreateDate>=TRUNC(#createDate#,'DD') AND
			// CreateDate<=TRUNC(#createDate#,'DD')+1]]>
			// sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB
			// + "<![CDATA[" + column + ">=TRUNC(#" + field + "#,'DD') AND " +
			// column + "<=TRUNC(#" + field + "#,'DD')+1]]>" + LITERAL_CRLF);
			// } else {
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + column + " = #" + fieldLowerCase + "#" + LITERAL_CRLF);
			// }
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "</isNotNull>" + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "</dynamic>" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</select>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createInsert(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<insert id=\"" + tableName + ".insert\" parameterMap=\"" + tableName + ".param\">" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "<![CDATA[" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "INSERT INTO " + this.tableName.toUpperCase() + " (" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column = (String) nativeColumnName.get(i);
			// String field;
			// column = (String) columnName.get(i);
			// char[] columnC = column.toCharArray();
			// columnC[0] = Character.toLowerCase(columnC[0]);
			// field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + column + (i == columnName.size() - 1 ? "" : ",") + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + ") values (" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "?" + (i == columnName.size() - 1 ? "" : ",") + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + ")" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "]]>" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</insert>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createUpdate(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<update id=\"" + tableName + ".update\" parameterMap=\"" + tableName + ".param\">" + LITERAL_CRLF);
		// <![CDATA[UserId = #userId#]]> </isNotNull>
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "<![CDATA[UPDATE " + this.tableName.toUpperCase() + "]]>" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "<dynamic prepend=\"SET\">" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column = (String) nativeColumnName.get(i);
			String field = (String) columnName.get(i);
			char[] columnC = field.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			String fieldLowerCase = new String(columnC);
			// column = (String) columnName.get(i);
			// char[] columnC = column.toCharArray();
			// columnC[0] = Character.toLowerCase(columnC[0]);
			// field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB);
			sqlMap += ("<isNotNull ");
			sqlMap += (StringUtil.padString("property=\"" + fieldLowerCase + "\"", 50, " ", StringUtil.PAD_ATREAR));
			sqlMap += ("prepend=\",\">");
			sqlMap += ("<![CDATA[" + StringUtil.padString(column, 35, " ", StringUtil.PAD_ATREAR) + " = " + StringUtil.padString("#" + fieldLowerCase + "#", 25, " ", StringUtil.PAD_ATREAR) + "]]>");
			sqlMap += ("</isNotNull>");
			sqlMap += (LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "</dynamic>" + LITERAL_CRLF);

		String column = (String) nativeColumnName.get(0);
		String field = (String) columnName.get(0);
		// column = (String) columnName.get(0);
		// char[] columnC = column.toCharArray();
		// columnC[0] = Character.toLowerCase(columnC[0]);
		// field = new String(columnC);

		sqlMap += (LITERAL_TAB + LITERAL_TAB + "WHERE " + column + "=#" + field + "#" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</update>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createDelete(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		String column = (String) nativeColumnName.get(0);
		String field = (String) columnName.get(0);
		char[] columnC = field.toCharArray();
		columnC[0] = Character.toLowerCase(columnC[0]);
		String fieldLowerCase = new String(columnC);
		// column = (String) columnName.get(0);
		// char[] columnC = column.toCharArray();
		// columnC[0] = Character.toLowerCase(columnC[0]);
		// field = new String(columnC);
		sqlMap += (LITERAL_TAB + "<delete id=\"" + tableName + ".delete\"" + " parameterClass=\"java.lang.String\">" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "DELETE * FROM " + this.tableName.toUpperCase() + " WHERE " + column + "=#" + fieldLowerCase + "#" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</delete>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String createAudit(String tableName, List<String> columnName, List<String> columnType, List<String> nativeColumnName, List<String> nativeColumnType) {
		String sqlMap = new String();
		sqlMap += (LITERAL_TAB + "<select id=\"" + tableName + ".audit\" parameterMap=\"" + tableName + ".param\" resultMap=\"" + tableName + ".result\">" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "SELECT *" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "FROM " + this.tableName.toUpperCase() + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "<dynamic prepend=\"WHERE\">" + LITERAL_CRLF);
		for (int i = 0; i < columnName.size(); i++) {
			String column = (String) nativeColumnName.get(i);
			String field = (String) columnName.get(i);
			char[] columnC = field.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			String fieldLowerCase = new String(columnC);
			// column = (String) columnName.get(i);
			// char[] columnC = column.toCharArray();
			// columnC[0] = Character.toLowerCase(columnC[0]);
			// field = new String(columnC);
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "<isNotNull property=\"" + fieldLowerCase + "\" prepend=\"AND\">" + LITERAL_CRLF);
			// if (columnType.get(i).equals("datetime")) {
			// <![CDATA[CreateDate>=TRUNC(#createDate#,'DD') AND
			// CreateDate<=TRUNC(#createDate#,'DD')+1]]>
			// sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB
			// + "<![CDATA[" + column + ">=TRUNC(#" + field + "#,'DD') AND " +
			// column + "<=TRUNC(#" + field + "#,'DD')+1]]>" + LITERAL_CRLF);
			// } else {
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + column + " = #" + fieldLowerCase + "#" + LITERAL_CRLF);
			// }
			sqlMap += (LITERAL_TAB + LITERAL_TAB + LITERAL_TAB + "</isNotNull>" + LITERAL_CRLF);
		}
		sqlMap += (LITERAL_TAB + LITERAL_TAB + "</dynamic>" + LITERAL_CRLF);
		sqlMap += (LITERAL_TAB + "</select>" + LITERAL_CRLF);
		sqlMap += (LITERAL_CRLF);
		return sqlMap;
	}

	private String getJdbcType(String type) {
		if (type.equals("varchar"))
			return "VARCHAR";
		if (type.equals("datetime"))
			return "TIMESTAMP";
		if (type.equals("char"))
			return "VARCHAR";
		if (type.equals("numeric"))
			return "NUMERIC";
		return "VARCHAR";
	}

	private String getJavaType(String type) {
		if (type.equals("varchar"))
			return "java.lang.String";
		if (type.equals("datetime"))
			return "java.util.Date";
		if (type.equals("char"))
			return "java.lang.String";
		if (type.equals("numeric"))
			return "java.lang.Double";
		return "java.lang.String";
	}

	public void generateClassDeffinition() {
		List<String> commonModelFields = getCommonModelFields();
		List<String> baseAuditableModelFields = getBaseAuditableModelFields();

		this.tableName = (String) this.cbTableName.getSelectedItem();
		List<String> columnName = new ArrayList<String>();
		List<String> columnType = new ArrayList<String>();

		try {
			rsMetaData = getColumns(this.catalog, this.tableName);
			columnName = createListFromMetaData(rsMetaData, 4, this.camelCase);
			rsMetaData = getColumnTypes(this.catalog, this.tableName);
			columnType = createListFromMetaData(rsMetaData, 6, false);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		String tableModelName = "";
		tableModelName = this.tableName.toLowerCase().substring(0, tableName.length() - (this.pluralTableNames ? 1 : 0));
		tableModelName = this.camelCase ? StringUtil.createCamelCase(tableModelName, "_") : tableModelName;

		columnName.removeAll(commonModelFields);
		columnName.removeAll(baseAuditableModelFields);

		String classDeff = new String();
		classDeff += createClassHeader();
		classDeff += ("public class " + tableModelName + "Model extends CommonModel {" + LITERAL_CRLF);
		classDeff += createClassFields(tableModelName, columnName, columnType);
		classDeff += LITERAL_CRLF;
		classDeff += createGetterSetter(tableModelName, columnName, columnType);
		classDeff += ("}");
		txtClassDeclaration.setText(classDeff);
	}

	private String createClassHeader() {
		String classDeff = new String();
		classDeff += ("/**" + LITERAL_CRLF);
		classDeff += (" * File Name	: " + tableName + "Model.java" + LITERAL_CRLF);
		classDeff += (" * Author		: Adelwin" + LITERAL_CRLF);
		classDeff += (" * Create Date	: " + new Date().toString() + LITERAL_CRLF);
		classDeff += (" *" + LITERAL_CRLF);
		classDeff += (" * Copyright (c) 2011 Adelwin. All Rights Reserved. <BR>" + LITERAL_CRLF);
		classDeff += (" * <BR>" + LITERAL_CRLF);
		classDeff += (" * This software contains confidential and proprietary information of" + LITERAL_CRLF);
		classDeff += (" * Adelwin. (\"Confidential Information\").<BR>" + LITERAL_CRLF);
		classDeff += (" * <BR>" + LITERAL_CRLF);
		classDeff += (" *" + LITERAL_CRLF);
		classDeff += (" *" + LITERAL_CRLF);
		classDeff += (" */" + LITERAL_CRLF);

		classDeff += LITERAL_CRLF;

		classDeff += ("package " + packageName + ";" + LITERAL_CRLF + LITERAL_CRLF);
		return classDeff;
	}

	private String createClassFields(String tableName, List<String> columnName, List<String> columnType) {
		String classDeff = new String();
		for (int i = 0; i < columnName.size(); i++) {
			String column;
			String field;
			column = (String) columnName.get(i);
			char[] columnC = column.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			field = new String(columnC);
			classDeff += (LITERAL_TAB + "private " + getJavaClass((String) columnType.get(i)) + " " + field + ";" + LITERAL_CRLF);
		}
		return classDeff;
	}

	private String createGetterSetter(String tableName, List<String> columnName, List<String> columnType) {
		String classDeff = new String();
		for (int i = 0; i < columnName.size(); i++) {
			String column;
			String field;
			column = (String) columnName.get(i);
			char[] columnC = column.toCharArray();
			columnC[0] = Character.toLowerCase(columnC[0]);
			field = new String(columnC);
			// getter
			classDeff += (LITERAL_TAB + "public " + getJavaClass((String) columnType.get(i)) + " get" + column + "() {" + LITERAL_CRLF);
			classDeff += (LITERAL_TAB + LITERAL_TAB + "return " + field + ";" + LITERAL_CRLF);
			classDeff += (LITERAL_TAB + "}" + LITERAL_CRLF);
			// setter
			classDeff += (LITERAL_TAB + "public void set" + column + "(" + getJavaClass((String) columnType.get(i)) + " " + field + ") {" + LITERAL_CRLF);
			classDeff += (LITERAL_TAB + LITERAL_TAB + "this." + field + " = " + field + ";" + LITERAL_CRLF);
			classDeff += (LITERAL_TAB + "}" + LITERAL_CRLF);
		}
		return classDeff;
	}

	private List<String> getCommonModelFields() {
		List<String> retVal = new ArrayList<String>();
		retVal.add("Attr1");
		retVal.add("Attr2");
		retVal.add("Attr3");
		retVal.add("Attr4");
		retVal.add("status");
		return retVal;
	}

	private List<String> getBaseAuditableModelFields() {
		List<String> retVal = new ArrayList<String>();
		retVal.add("TableName");
		retVal.add("CreateBy");
		retVal.add("CreateDate");
		retVal.add("UpdateBy");
		retVal.add("UpdateDate");
		return retVal;
	}

	private String getJavaClass(String type) {
		if (StringUtil.containsIgnoreCase(type, "varchar"))
			return "String";
		if (StringUtil.containsIgnoreCase(type, "date"))
			return "Date";
		if (StringUtil.containsIgnoreCase(type, "char"))
			return "Date";
		if (StringUtil.containsIgnoreCase(type, "numeric"))
			return "Double";
		return "String";
	}
}
